--CREATE First Database for encryption and decryption testing
--USE master;
--GO
--CREATE DATABASE EncryptionTest
--ON 
--( NAME = EncryptionTest_Data,
--    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Encry1Data.mdf',
--    SIZE = 3,
--    MAXSIZE = 5,
--    FILEGROWTH = 1 )
--LOG ON
--( NAME = EncryptionTest_Log,
--    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Encry1Log.ldf',
--    SIZE = 1MB,
--    MAXSIZE = 5MB,
--    FILEGROWTH = 1MB ) ;
--GO


--Use EncryptionTest
--GO
--Create Table emps
--(
--      name varchar(20),
--      dept varchar(20),
--      EncryptedName Varbinary(256),
--      DecryptedName Varchar(20)
--)

--Insert into emps (Name,Dept)
--Select 'Ritesh','MIS' union all
--Select 'Rajan','Acct'
--union all
--Select 'Bhaumik','IT'

--Select * from emps
--go


--create one Database Master Key
--Create master key
--Encryption by Password ='p@Ss28'

--Create One Certificate
--Create Certificate AdvCert
--With Subject= 'Certificate to encrypt emps table',
--Start_date = '2009-09-29',
--Expiry_date ='2012-02-07'
--GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@Ss28'
GO

--create symmetric key
Create Symmetric Key AdvSym
With Algorithm = AES_256
Encryption by Certificate AdvCert
GO

--open symmetric key
Open Symmetric key AdvSym
Decryption by certificate AdvCert
GO

--check encryption function whether it is working or not
Select Name, Dept, EncryptByKey(Key_Guid(N'AdvSym'), Name) as EncryptedName from emps
go

Close Symmetric key AdvSym

--there is null in EncryptedName column right now,
--we are updating EncryptedName column with encrypted data
--update emps set EncryptedName=EncryptByKey(Key_Guid(N'AdvSym'),Name)
--GO

--look at the status of data now.
--select * from emps
--GO


--let us check whether decryption is working or not
--Select Name,Dept,EncryptedName,
--Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
--go

--update emps table DecryptedName column with Decrypted data
--Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
--GO




--since we want to backup DMK and want to restore it in 
--different server, I am removing encryption by SMK
--as SMK is instance based, it may be possible that
--you are trying to restore database in different instance
--ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

--again opening master key before taking up backup
--OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@Ss28'

--backing up master key to physical file
--BACKUP MASTER KEY TO FILE = 'C:\Encry1.DMK'
--ENCRYPTION BY PASSWORD='p@Ss28'
GO

--now backing up database Encry1
--BACKUP DATABASE EncryptionTest
--  TO DISK = 'C:\EncryptionTest.bak' 
GO
